package com.hfjyz.registration.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.hfjyz.registration.model.OrderInfo;
import com.hfjyz.registration.object.OrderAnalyzeInfoBean;

public class OrderInfoDaoImpl extends BaseDaoImpl<OrderInfo, String> implements
		OrderInfoDao {

	protected OrderInfoDaoImpl() {
		super(OrderInfo.class);
	}

	@SuppressWarnings("unchecked")
	public OrderInfo findByUserIdAndExamId(Integer userId, Integer examId)
			throws Exception {
		List<OrderInfo> applies = getHibernateTemplate()
				.find("from OrderInfo as orderInfo where orderInfo.userId = ? and orderInfo.examId = ? and orderInfo.deleteFlg = 0",
						new Object[] { userId, examId });
		if (applies == null || applies.isEmpty()) {
			return null;
		}
		return applies.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrderAnalyzeInfoBean> findOrderAnalyzeByExam() throws Exception {

		final String sql = "select sum(case when o.orderAmount is null then 0 else o.orderAmount end) as totalAmount, "
				+ " sum(case when o.orderState = 1 then 1 else 0 end) as totalPaied, "
				+ " e.examName as examName "
				+ " from "
				+ " (select id as id, name as examName from exam) e "
				+ " left join (select orderAmount, examId, orderState from orderInfo where orderState = 1 and deleteFlg = 0) o on o.examId = e.id "
				+ " group by e.examName";
		List<OrderAnalyzeInfoBean> apply = (List<OrderAnalyzeInfoBean>) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						List<Object[]> o = session.createSQLQuery(sql).list();
						if (o == null || o.isEmpty()) {
							return new ArrayList<OrderAnalyzeInfoBean>(0);
						}
						List<OrderAnalyzeInfoBean> result = new ArrayList<OrderAnalyzeInfoBean>(o.size());
						for (Object[] row : o) {
							OrderAnalyzeInfoBean oa = new OrderAnalyzeInfoBean();
							oa.setTotalAmount(row[0] == null ? 0 : ((Number)row[0]).doubleValue());
							oa.setTotalPaied(row[1] == null ? 0 : ((Number)row[1]).intValue());
							oa.setExamName((String)row[2]);
							result.add(oa);
						}
						return result;
					}
				});
		return apply;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrderAnalyzeInfoBean> findOrderAnalyzeByOffice(final Integer examId)
			throws Exception {

		final String sql = "select sum(case when o.orderAmount is null then 0 else o.orderAmount end) as totalAmount, "
				+ " sum(case when o.orderState = 1 then 1 else 0 end) as totalPaied, "
				+ " office.officeCode as officeCode, "
				+ " office.officeName as officeName, "
				+ " d.departName as departName "
				+ " from "
				+ " (select id, code as officeCode, departId, name as officeName, examId from Office) office "
				+ " left join (select id, name as departName from Depart) d on d.id = office.departId "
				+ " left join (select orderAmount, examId, orderState from orderInfo where orderState = 1 and deleteFlg = 0) o on o.examId = office.examId "
				+ " where office.examId = ? "
				+ " group by office.officeCode, office.officeName, d.departName ";
		List<OrderAnalyzeInfoBean> apply = (List<OrderAnalyzeInfoBean>) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						List<Object[]> o = session.createSQLQuery(sql)
								.setParameter(0, examId).list();
						if (o == null || o.isEmpty()) {
							return new ArrayList<OrderAnalyzeInfoBean>(0);
						}
						List<OrderAnalyzeInfoBean> result = new ArrayList<OrderAnalyzeInfoBean>(o.size());
						for (Object[] row : o) {
							OrderAnalyzeInfoBean oa = new OrderAnalyzeInfoBean();
							oa.setTotalAmount(row[0] == null ? 0 : ((Number)row[0]).doubleValue());
							oa.setTotalPaied(row[1] == null ? 0 : ((Number)row[1]).intValue());
							oa.setOfficeCode((String)row[2]);
							oa.setOfficeName((String)row[3]);
							oa.setDepartName((String)row[4]);
							result.add(oa);
						}
						return result;
					}
				});
		return apply;
	}
	public List<OrderInfo> findByState(Integer state) throws Exception {
		List<OrderInfo> orders = getHibernateTemplate()
				.find("from OrderInfo as orderInfo where orderInfo.orderState = ? and orderInfo.deleteFlg = 0",
						new Object[] { state });
		return orders;
	}
}
